package peter.webtools.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;
import java.sql.Types;

import peter.webtools.beans.Task;

@Repository
public class TaskDao extends BaseDao {
    private static final Logger log = LoggerFactory.getLogger(TaskDao.class);
    private static TaskDao dao = null;
    @Resource
    private JdbcTemplate jdbcTemplate;
    private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

    public boolean createTask(String name) {
        String sql = "insert into tasks(name) values('" + name + "')";
        jdbcTemplate.execute(sql);
        return true;
    }

    public boolean createTask(Task task) {
        String sql = "insert into tasks(name, class, tags, details, pre_id) values(?, ?, ?, ?, ?)";
        int result = jdbcTemplate.update(sql,
                new Object[] {task.getName(), task.getClassId(), task.getTags(), task.getDetails(), task.getPreId()},
                new int[] {java.sql.Types.VARCHAR, java.sql.Types.INTEGER, java.sql.Types.VARCHAR, java.sql.Types.VARCHAR, java.sql.Types.INTEGER});
        return result > 0;
    }

    /**
     * 复制一条纪录。
     * 在复制这条纪录之前，我们是否应该保证这条纪录时存在的。
     * @temp by peter: 需要验证sql_copy语句是否正确。
     */
    public boolean copyTask(int id) {
        if(this.getTask(id) == null) {
            // 这里需要一条错误的log或者一个异常，该如何选择呢？
            log.error("Copy task 失败，因为id（" + id + "）不存在。");
            return false;
        }

        String sql_end = "update tasks set endtime = TIMESTAMP('" + this.sdf.format(new Timestamp(System.currentTimeMillis())) + "') where id = (select max(id) from tasks) and endtime is null";
        String sql_copy = "insert into tasks(name, details, pre_id) select name, details, id as pre_id from tasks where id = " + id;

        int[] results = jdbcTemplate.batchUpdate(sql_end, sql_copy);

        if(results[0] <=0 || results[1] <=0 || results[2] <=0 ) {
            log.error("复制Task失败");
            return false;
        }

        return true;
    }

    /**
     * 获得最终的id。
     * 像这样的逻辑，除了这样使用jdbc和使用存储过程之外，还有其他方式吗？
     * @temp by peter: 这种方式是不对的。
     */
    private int getUltimaId(int id) {
        String sql = "select nextid from tasks where id = " + id;
        return 0;
    }

    public List<Task> getTasks() {
        List<Task> list = new ArrayList<Task>();
        String sql = "select tasks.*, task_class.name as class_name from tasks, task_class where tasks.class = task_class.id";

        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                Task task = new Task();
                task.setId(rs.getInt("id"));
                task.setName(rs.getString("name"));
                task.setClassId(rs.getInt("class"));
                task.setClassName(rs.getString("class_name"));
                task.setTagsByString(rs.getString("tags"));
                task.setStartTime(rs.getTimestamp("starttime"));
                task.setEndTime(rs.getTimestamp("endtime"));
                task.setDetails(rs.getString("details"));
                task.setPreId(rs.getInt("pre_id"));
                list.add(task);
            }
        });
        
        return list;
    }

    /**
     * 根据id获取任务。
     */
    public Task getTask(int id) {
        String sql = "select * from tasks where id = " + id;
        List<Task> list = new ArrayList<Task>();

        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                Task task = new Task();
                task.setId(rs.getInt("id"));
                task.setName(rs.getString("name"));
                task.setStartTime(rs.getTimestamp("starttime"));
                task.setEndTime(rs.getTimestamp("endtime"));
                task.setDetails(rs.getString("details"));
                task.setPreId(rs.getInt("pre_id"));
                list.add(task);
            }
        });

        return list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 查看当前的未完成的任务。
     * 有可能返回为空。
     */
    public Task current() {
        String sql = "select * from tasks where id = (select max(id) from tasks) and end_time is null";
        List<Task> list = new ArrayList<Task>();

        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                Task task = new Task();
                task.setId(rs.getInt("id"));
                task.setName(rs.getString("name"));
                task.setStartTime(rs.getTimestamp("start_time"));
                task.setEndTime(rs.getTimestamp("end_time"));
                task.setDetails(rs.getString("details"));
                task.setPreId(rs.getInt("pre_id"));
                list.add(task);
            }
        });

        return list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 查看最后一个任务。
     */
    public Task lastTask() {
        String sql = "select * from tasks where id = (select max(id) from tasks)";
        List<Task> list = new ArrayList<Task>();

        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                Task task = new Task();
                task.setId(rs.getInt("id"));
                task.setName(rs.getString("name"));
                task.setStartTime(rs.getTimestamp("starttime"));
                task.setEndTime(rs.getTimestamp("endtime"));
                task.setDetails(rs.getString("details"));
                task.setPreId(rs.getInt("pre_id"));
                list.add(task);
            }
        });

        return list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 结束正在进行的任务。
     * @temp by peter: 没有验证失败的逻辑。
     * @temp by peter: 没有检验是否存在不合法数据的线程。
     */
    public boolean endTask() {
        String sql = "update tasks set endtime = ? where id = (select max(id) from tasks) and endtime is null";
        int result = 0;

        try {
            result = jdbcTemplate.update(sql, new Timestamp(System.currentTimeMillis()));
        }
        catch(Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    /**
     * 根据ID结束任务。
     */
    public boolean endTask(int id) {
        String sql = "update tasks set endtime = ? where id = ? and endtime is null";
        int result = 0;

        try {
            // result = jdbcTemplate.update(sql, new Object[] {new Timestamp(System.currentTimeMillis()), id});
            result = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement pstmt) throws SQLException {
                    pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                    pstmt.setInt(2, id);
                }
            });
        }
        catch(Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }
}

class TaskRowCallbackHandler implements RowCallbackHandler {
    private List<Task> list = new ArrayList<Task>();

    public void processRow(ResultSet rs) throws SQLException {
        Task task = new Task();
        task.setId(rs.getInt("id"));
        task.setName(rs.getString("name"));
        System.out.println(rs.getString("name"));
    }
}
